﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using Newtonsoft.Json;
using System.Data.Linq;
using System.Reflection;
using System.Text;

namespace winproject.Models
{
    /// <summary>
    /// ProductDetails:: Contains overview details for a product
    /// </summary>
    public class ProductDetails
    {
        public int nPid { get; set; }
        public int nCid { get; set; }
        public string sCatName { get; set; }

        public int nSCid { get; set; }
        public string sSubCatName { get; set; }

        public string sTitle { get; set; }
        public string sSlug { get; set; }
        public string sDesc { get; set; }
        public string sHomelnk { get; set; }
        public string sDownloadlnk { get; set; }
        public string sBuyNowlnk { get; set; }
        public string sCompanyName { get; set; }
        
        public decimal dBasePrice { get; set; }
        
        public int nCurrency { get; set; }
        public string sCurSymbol { get; set; }

        public int nDiscountOffered { get; set; }
        public decimal dNetPrice { get; set; }

        public DateTime? dtPModified { get; set; }
        public short nProductStatus { get; set; }
        public short nImgCounts { get; set; }

    }

    /// <summary>
    /// ProductProperties:: Contains extra details of product 
    /// </summary>
    public class ProductProperties
    {
        public int nPid { get; set; }
        public string sTocLink { get; set; }
        public string sEulaLink { get; set; }
        public string sExeName { get; set; }
        public int? nSizeInBytes { get; set; }
        public string sMD5CheckSum { get; set; }
        public string sAuthorName { get; set; }
        public bool? _isDigitalSigned { get; set; }
        public bool? _isMultilingual { get; set; }
    }

    /// <summary>
    /// ProductImages:: Contains list of images uploaded for a product
    /// </summary>
    public class ProductImages
    {
        public int nPImgid { get; set; }
        public int nPid { get; set; }
        public string sImagePath { get; set; }
        public string sImageThumbPath { get; set; }
        public bool _isDefaultImg { get; set; }
        public short status { get; set; }
    }

    /// <summary>
    /// ProductStats:: Contains unique stats for a product
    /// </summary>
    public class ProductStats
    {
        public int nStatsId { get; set; }
        public int nPid { get; set; }
        public int? nPRanking { get; set; }
        public int? nDownloadCount { get; set; }
        public int? nVisitedCount { get; set; }
        public int? nEditorsRating { get; set; }
        public int? nDownloadsRating { get; set; }
    }

    

    /// <summary>
    /// SubCatFeatures:: Contains list of unique features for each subcategory
    /// </summary>
    public class SubCatFeatures
    {
        public int nFid { get; set; }
        public int nCid { get; set; }
        public int nScid { get; set; }
        public string sFeatureTag { get; set; }
    }

    public class ProductStatsModel
    {
        public int nStatsId { get; set; }
        public int nPid { get; set; }
        public int? nPRanking { get; set; }
        public int? nDownloadCount { get; set; }
        public int? nVisitedCount { get; set; }
        public int? nEditorsRating { get; set; }
        public int? nDownloadsRating { get; set; }

        #region Add New Item Detail
        public ReturnResult AddNewProdStats(ProductStats newItem, bool _isEditReq = false)
        {
            ReturnResult oResult = null;


            try
            {
                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                var oItemupdate = oWinContext.Product_Stats.Where(c => c.PID==newItem.nPid).FirstOrDefault();
                Product_Stat oItem = new Product_Stat();
                if (oWinContext.DatabaseExists())
                {
                    oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.RecordNotInserted, msgJson = "OOps Something went wrong !!" };
                    if (oItemupdate != null)
                    {
                        //simply update virus info
                        //oItemupdate.PID = newItem.nPid;
                        oItemupdate.Ranking = newItem.nPRanking;
                        oItemupdate.DownloadCounts = newItem.nDownloadCount;
                        oItemupdate.DownloadRatings = newItem.nDownloadsRating;
                        oItemupdate.VisitedCounts = newItem.nVisitedCount;
                        oItemupdate.EditorRatings = newItem.nEditorsRating;


                        oWinContext.SubmitChanges();
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Updated") };
                    }
                    else
                    {
                        oItem.PID= newItem.nPid;
                        oItem.Ranking = newItem.nPRanking;
                        oItem.DownloadCounts = newItem.nDownloadCount;
                        oItem.DownloadRatings = newItem.nDownloadsRating;
                        oItem.VisitedCounts = newItem.nVisitedCount;
                        oItem.EditorRatings = newItem.nEditorsRating;

                        oWinContext.Product_Stats.InsertOnSubmit(oItem);
                        oWinContext.SubmitChanges();
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Added") };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

        #region Find product stats - Filters : ID, or Get ALL Item details
        public ReturnResult getProdStats(out List<ProductStats> oIDetailList, ProductStats oSearchItem = null)
        {
            ReturnResult oResult = null;
            oIDetailList = null;
            try
            {

                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                if (oWinContext.DatabaseExists())
                {
                    oIDetailList = new List<ProductStats>();
                    if (oSearchItem != null && (oSearchItem.nPid)>0)
                    {
                        //search request
                        oIDetailList = oWinContext.Product_Stats.Where(c => c.PID == oSearchItem.nPid)
                                                                .Select(c => new ProductStats()
                                                                {
                                                                    nPid = c.PID,
                                                                    nDownloadCount=c.DownloadCounts,
                                                                    nDownloadsRating=c.DownloadRatings,
                                                                    nEditorsRating=c.EditorRatings,
                                                                    nPRanking=c.Ranking,
                                                                    nVisitedCount=c.VisitedCounts
                                                                }).ToList<ProductStats>();
                    }

                    //var lsCategories = qCatList.ToList<Category>();
                    string resultJson = JsonConvert.SerializeObject(oIDetailList);
                    if (oIDetailList != null && oIDetailList.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(resultJson))
                        {
                            oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = resultJson };
                        }
                    }
                    else
                    {
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.NoRecordsFound, msgJson = resultJson };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

    }

    public class ProductPropertiesModel
    {
        public int nPid { get; set; }
        public string sTocLink { get; set; }
        public string sEulaLink { get; set; }
        public string sExeName { get; set; }
        public int nSizeInBytes { get; set; }
        public string sMD5CheckSum { get; set; }
        public string sAuthorName { get; set; }
        public bool _isDigitalSigned { get; set; }
        public bool _isMultilingual { get; set; }

        #region Add New Item Detail
        public ReturnResult AddNewProdProperty(ProductProperties newItem, bool _isEditReq = false)
        {
            ReturnResult oResult = null;


            try
            {
                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                var oItemupdate = oWinContext.Product_Properties.Where(c=>c.PID == newItem.nPid).FirstOrDefault();
                Product_Property oItem = new Product_Property();
                if (oWinContext.DatabaseExists())
                {
                    oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.RecordNotInserted, msgJson = "OOps Something went wrong !!" };
                    if (oItemupdate != null)
                    {
                        //simply update virus info
                        //oItemupdate.PID = newItem.nPid;
                        oItemupdate.Author = newItem.sAuthorName;
                        oItemupdate.EulaLink = newItem.sEulaLink;
                        oItemupdate.TOCLink = newItem.sTocLink;
                        //oItemupdate.Version = newItem.;
                        oItemupdate.ExeName = newItem.sExeName;
                        oItemupdate.FileSize = newItem.nSizeInBytes;
                        oItemupdate.MD5Checksum = newItem.sMD5CheckSum;
                        oItemupdate.IsDigitalSigned = newItem._isDigitalSigned;
                        oItemupdate.IsMultilingual = newItem._isMultilingual;


                        oWinContext.SubmitChanges();
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Updated") };
                    }
                    else
                    {
                        oItem.PID = newItem.nPid;
                        oItem.Author = newItem.sAuthorName;
                        oItem.EulaLink = newItem.sEulaLink;
                        oItem.TOCLink = newItem.sTocLink;
                        oItem.ExeName = newItem.sExeName;
                        oItem.FileSize = newItem.nSizeInBytes;
                        oItem.MD5Checksum = newItem.sMD5CheckSum;
                        oItem.IsDigitalSigned = newItem._isDigitalSigned;
                        oItem.IsMultilingual = newItem._isMultilingual;

                        oWinContext.Product_Properties.InsertOnSubmit(oItem);
                        oWinContext.SubmitChanges();
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Added") };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

        #region Find product property - Filters : ID, or Get ALL Item details
        public ReturnResult getProdProperties(out List<ProductProperties> oIDetailList, ProductProperties oSearchItem = null)
        {
            ReturnResult oResult = null;
            oIDetailList = null;
            try
            {

                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                if (oWinContext.DatabaseExists())
                {
                    oIDetailList = new List<ProductProperties>();
                    if (oSearchItem != null && (oSearchItem.nPid) > 0)
                    {
                        //search request
                        oIDetailList = oWinContext.Product_Properties.Where(c => c.PID == oSearchItem.nPid)
                                                                .Select(c => new ProductProperties()
                                                                {
                                                                    nPid = c.PID,
                                                                    _isDigitalSigned=c.IsDigitalSigned,
                                                                    _isMultilingual=c.IsMultilingual,
                                                                    nSizeInBytes=c.FileSize,
                                                                    sAuthorName=c.Author,
                                                                    sEulaLink=c.EulaLink,
                                                                    sExeName=c.ExeName,
                                                                    sMD5CheckSum=c.MD5Checksum,
                                                                    sTocLink=c.TOCLink
                                                                }).ToList<ProductProperties>();
                    }

                    //var lsCategories = qCatList.ToList<Category>();
                    string resultJson = JsonConvert.SerializeObject(oIDetailList);
                    if (oIDetailList != null && oIDetailList.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(resultJson))
                        {
                            oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = resultJson };
                        }
                    }
                    else
                    {
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.NoRecordsFound, msgJson = resultJson };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion
    }

    public class ProductImagesModel
    {
        public int nId { get; set; }
        public int nPid { get; set; }
        public string sImgPath { get; set; }
        public string sThumbPath { get; set; }
        public byte nStatus { get; set; }

        #region Add New Item Detail
        public ReturnResult AddNewProdImage(ProductImages newItem, bool _isEditReq = false)
        {
            ReturnResult oResult = null;


            try
            {
                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                //var oItemupdate = oWinContext.Product_Images.Where(c => c.PID == newItem.nPid).FirstOrDefault();
                Product_Image oItem = new Product_Image();
                if (oWinContext.DatabaseExists())
                {
                    oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.RecordNotInserted, msgJson = "OOps Something went wrong !!" };
                    oItem.ImagePath = newItem.sImagePath;
                    oItem.ThumbImagePath = newItem.sImageThumbPath;
                    oItem.Status =(byte)newItem.status;
                    oItem.PID = newItem.nPid;
                    oWinContext.Product_Images.InsertOnSubmit(oItem);
                    oWinContext.SubmitChanges();
                    
                    oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Added") };
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

       
       #region Delete Product Image
        public ReturnResult deleteProductImage(ProductImages newItem)
        {
            ReturnResult oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.BadRequest, msgJson = "[]" };
            try
            {
                if (newItem.nPid > 0)
                {
                    ProductImages oItem = new ProductImages();
                    oItem.nPImgid = newItem.nPImgid;
                    oItem.nPid=newItem.nPid;

                    var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                    if (oWinContext.DatabaseExists())
                    {
                        Product_Image oProduct = oWinContext.Product_Images.Where(x => x.ID == oItem.nPImgid && x.PID==oItem.nPid).FirstOrDefault();
                        if (oProduct.ID> 0)
                        {
                            //means record exist
                            oWinContext.Product_Images.DeleteOnSubmit(oProduct);
                            oWinContext.SubmitChanges(ConflictMode.FailOnFirstConflict);
                            if (oProduct != null && oProduct.ID > 0)
                            {
                                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = "Record removed successfully" };                                
                            }
                            else
                            {
                                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.RecordNotDeleted, msgJson = "No record deleted" };
                            }
                        }
                        else
                        {
                            oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.NoRecordsFound, msgJson = "No record found" };
                        }
                    }
                }
            }
            catch (ChangeConflictException e)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", e.ToString()) };
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion


        #region Find product Image - Filters : ID, or Get ALL Item details
        public ReturnResult getProdImages(out List<ProductImages> oIDetailList, ProductImages oSearchItem = null)
        {
            ReturnResult oResult = null;
            oIDetailList = null;
            try
            {

                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                if (oWinContext.DatabaseExists())
                {
                    oIDetailList = new List<ProductImages>();
                    if (oSearchItem != null && (oSearchItem.nPid) > 0)
                    {
                        //search request
                        oIDetailList = oWinContext.Product_Images.Where(c => c.PID == oSearchItem.nPid)
                                                                .Select(c => new ProductImages()
                                                                {
                                                                    nPid = c.PID,
                                                                    sImagePath=c.ImagePath,
                                                                    sImageThumbPath=c.ThumbImagePath,
                                                                    status=c.Status,
                                                                    nPImgid=c.ID
                                                                }).ToList<ProductImages>();
                    }

                    //var lsCategories = qCatList.ToList<Category>();
                    string resultJson = JsonConvert.SerializeObject(oIDetailList);
                    if (oIDetailList != null && oIDetailList.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(resultJson))
                        {
                            oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = resultJson };
                        }
                    }
                    else
                    {
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.NoRecordsFound, msgJson = resultJson };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion
    }

    public class ProductsModel
    {
        public int nPMid { get; set; }
        public int nCMid { get; set; }
        public int nSCMid { get; set; }
        public string sPTitle { get; set; }
        public string sPSlug { get; set; }
        public string sPDesc { get; set; }
        public string sPHomelnk { get; set; }
        public string sPDownloadlnk { get; set; }
        public string sPBuyNowlnk { get; set; }
        public string sPCompanyName { get; set; }
        public short nPImgCounts { get; set; }
        public decimal dPBasePrice { get; set; }
        public string sPCurrency { get; set; }
        public int nPDiscountOffered { get; set; }
        public decimal dPNetPrice { get; set; }
        public byte? nProductStatus { get; set; }

        DBClass oDB = new DBClass();

        #region Add New Product Detail
        public ReturnResult AddNewProduct(ProductDetails newItem, bool _isEditReq = false)
        {
            ReturnResult oResult = null;
            try
            {
                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                var oProductUpdate = oWinContext.Products.Where(c => c.ID == newItem.nPid).FirstOrDefault();
                Product oProduct = new Product();
                if (oWinContext.DatabaseExists())
                {
                    oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.RecordNotInserted, msgJson = "OOps Something went wrong !!" };
                    if (oProductUpdate != null)
                    {
                        //simply update product info
                        oProductUpdate.Title = newItem.sTitle;
                        oProductUpdate.Cat_ID = newItem.nCid;
                        oProductUpdate.Sub_Cat_ID = newItem.nSCid;
                        oProductUpdate.slug = newItem.sSlug;
                        oProductUpdate.Description = newItem.sDesc;
                        oProductUpdate.HomeLink = newItem.sHomelnk;
                        oProductUpdate.DownloadLink = newItem.sDownloadlnk;
                        oProductUpdate.BuyNowLink = newItem.sBuyNowlnk;
                        oProductUpdate.CompanyName = newItem.sCompanyName;
                        oProductUpdate.CurrencyId = newItem.nCurrency;
                        oProductUpdate.BasePrice = newItem.dBasePrice;
                        oProductUpdate.DiscountOffered = newItem.nDiscountOffered;
                        oProductUpdate.NetPrice = newItem.dNetPrice;
                        oProductUpdate.DateModified = DateTime.Now;

                        if (!_isEditReq)
                            oProductUpdate.Status = 1;

                        oWinContext.SubmitChanges();
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Updated") };
                    }
                    else
                    {

                        oProduct.Title = newItem.sTitle;
                        oProduct.Cat_ID = newItem.nCid;
                        oProduct.Sub_Cat_ID = newItem.nSCid;
                        oProduct.slug = newItem.sSlug;
                        oProduct.Description = newItem.sDesc;
                        oProduct.HomeLink = newItem.sHomelnk;
                        oProduct.DownloadLink = newItem.sDownloadlnk;
                        oProduct.BuyNowLink = newItem.sBuyNowlnk;
                        oProduct.CompanyName = newItem.sCompanyName;
                        oProduct.CurrencyId = newItem.nCurrency;
                        oProduct.BasePrice = newItem.dBasePrice;
                        oProduct.DiscountOffered = newItem.nDiscountOffered;
                        oProduct.DateAdded = DateTime.Now;
                        oProduct.DateModified = DateTime.Now;
                        oProduct.NetPrice = newItem.dNetPrice;
                        oProduct.Status = 1;
                        oWinContext.Products.InsertOnSubmit(oProduct);
                        oWinContext.SubmitChanges();

                        //add empty record for products child tables
                        //oWinContext.Product_Properties.InsertOnSubmit(new Product_Property());
                        //oWinContext.Product_Stats.InsertOnSubmit(new Product_Stat());

                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = string.Format("Record {0} Successfully !!", "Added") };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

        #region Find Basic Product Details - Filters : Slug, ProductID, or Get ALL Product details
        public ReturnResult getProductDetails(out List<ProductDetails> oPDetailList, ProductDetails oSearchItem = null)
        {
            ReturnResult oResult = null;
            oPDetailList = null;
            DataSet oDataSet = null;
            try
            {

                var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                if (oWinContext.DatabaseExists())
                {
                    oPDetailList = new List<ProductDetails>();
                    string sSearchQuery= @"select 
	                                                        P.ID as nPid,
	                                                        P.Cat_ID as nCid,
	                                                        C.Cat_Name as sCatName,
	                                                        P.Sub_Cat_ID as nSCid,
	                                                        SC.Sub_Cat_Name as sSubCatName,
	                                                        P.Title as sTitle,
	                                                        P.slug as sSlug,
                                                            p.Description as sDesc,
                                                            P.HomeLink as sHomelnk,	                                                        
                                                            P.DownloadLink as sDownloadlnk,
	                                                        P.BuyNowLink as sBuyNowlnk,
	                                                        P.CompanyName as sCompanyName,
                                                            P.BasePrice as dBasePrice,
	                                                        P.CurrencyId as nCurrency,
	                                                        CU.ISO as sCurSymbol,
	                                                        P.DiscountOffered as nDiscountOffered,
	                                                        P.NetPrice as dNetPrice,
	                                                        P.DateModified as dtPModified,
	                                                        P.Status
                                                        from 
	                                                        (
	                                                          (Products as P Join Category as C on P.Cat_ID=C.ID) 
				                                                        Join 
	                                                          Sub_Category as SC on P.Sub_Cat_ID=SC.ID
	                                                        )
	                                                        Join Currency as CU on P.CurrencyId=CU.ID
                                                    ";

                    StringBuilder oStrBuiler = new StringBuilder();
                    if (oSearchItem != null && (!string.IsNullOrEmpty(oSearchItem.sTitle) || !string.IsNullOrEmpty(oSearchItem.sSlug))) //slug or title basis
                    {
                        //search request
                        //oPDetailList = 
                        string sSearchStr = (!string.IsNullOrEmpty(oSearchItem.sSlug) ? oSearchItem.sSlug : oSearchItem.sTitle);
                        string sPSearch =string.Format(@" where (P.Title like '%{0}%' or P.slug like '%{0}%')",sSearchStr);
                        //oDataSet = oDB.SelectData(string.Format("{0}{1}", sSearchQuery, sPSearch));
                        oDataSet=   oDB.SelectData(sSearchQuery, 
                                                         " (P.Title like '@PSearch' or P.slug like '@PSearch') ", 
                                                         new string[] { "PSearch" }, 
                                                         new byte[] { (byte)DBClass.DataType.StringLike }, 
                                                         new object[] { sSearchStr }
                                    );
                         
                    }
                    else if (oSearchItem != null && (oSearchItem.nCid) > 0 && (oSearchItem.nSCid > 0)) //category and sub category basis
                    {

                        oDataSet = oDB.SelectData(sSearchQuery,
                                                         " P.Cat_ID=@PCID and P.Sub_Cat_ID=@PSCID",
                                                         new string[] { "PCID", "PSCID" },
                                                         new byte[] { (byte)DBClass.DataType.Number, (byte)DBClass.DataType.Number },
                                                         new object[] { oSearchItem.nCid,oSearchItem.nSCid }
                                                     );
                    }
                    else if (oSearchItem != null && (oSearchItem.nCid) > 0) // category basis
                    {
                        oDataSet = oDB.SelectData(sSearchQuery,
                                                         " P.Cat_ID=@PCID",
                                                         new string[] { "PCID" },
                                                         new byte[] { (byte)DBClass.DataType.Number },
                                                         new object[] { oSearchItem.nCid }
                                                     );
                    }
                    else if (oSearchItem != null && (oSearchItem.nSCid) > 0) //subcategory basis
                    {
                        oDataSet = oDB.SelectData(sSearchQuery,
                                                         " P.PSCID=@PSCID",
                                                         new string[] { "PSCID" },
                                                         new byte[] { (byte)DBClass.DataType.Number },
                                                         new object[] { oSearchItem.nSCid }
                                                     );
                    }
                    
                    else if (oSearchItem != null && (oSearchItem.nPid) > 0) //product id basis
                    {
                        oDataSet = oDB.SelectData(sSearchQuery,
                                                         " P.ID=@PID",
                                                         new string[] { "PID" },
                                                         new byte[] { (byte)DBClass.DataType.Number },
                                                         new object[] { oSearchItem.nPid }
                                                     );
                    }
                    else
                    {
                        oDataSet = oDB.SelectData(sSearchQuery);
                    }

                    if (oDataSet != null)
                    {
                        if (oDataSet.Tables != null && oDataSet.Tables.Count > 0)
                        {
                            if (oDataSet.Tables[0].Rows != null && oDataSet.Tables[0].Rows.Count > 0)
                            {
                                oPDetailList = ToList<ProductDetails>(oDataSet.Tables[0]);

                                //oPDetailList = oDataSet.Tables[0].AsEnumerable().Select(
                                //                   x => new ProductDetails()
                                //                   {
                                //                       nPid = x.Field<int>("nPid"),
                                //                       nCid = x.Field<int>("nCid"),
                                //                       sCatName = x.Field<string>("sCatName"),
                                //                       nSCid = x.Field<int>("nSCid"),
                                //                       sSubCatName = x.Field<string>("sSubCatName"),
                                //                       sTitle = x.Field<string>("sTitle"),
                                //                       sSlug = x.Field<string>("sSlug"),
                                //                       sDesc = x.Field<string>("sDesc"),
                                //                       sBuyNowlnk = x.Field<string>("sBuyNowlnk"),
                                //                       sCompanyName = x.Field<string>("sCompanyName"),
                                //                       sHomelnk = x.Field<string>("sHomelnk"),
                                //                       nCurrency = x.Field<int>("nCurrency"),
                                //                       sCurSymbol = x.Field<string>("sCurSymbol"),
                                //                       dBasePrice = x.Field<decimal>("dBasePrice"),
                                //                       nDiscountOffered = x.Field<int>("nDiscountOffered"),
                                //                       dNetPrice = x.Field<decimal>("dNetPrice"),
                                //                       dtPModified = x.Field<DateTime?>("dtPModified"),
                                //                       nProductStatus = x.Field<short>("Status")
                                //                   }
                                //    ).ToList<ProductDetails>();
                            }
                        }
                    }
                    //var lsCategories = qCatList.ToList<Category>();
                    string resultJson = JsonConvert.SerializeObject(oPDetailList);
                    if (oPDetailList != null && oPDetailList.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(resultJson))
                        {
                            oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = resultJson };
                        }
                    }
                    else
                    {
                        oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.NoRecordsFound, msgJson = resultJson };
                    }
                }
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

        #region Delete Product Detail
        public ReturnResult deleteProduct(ProductDetails newItem)
        {
            ReturnResult oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.BadRequest, msgJson = "[]" };
            try
            {
                if (newItem.nPid > 0)
                {
                    Product oItem = new Product();
                    oItem.ID = newItem.nPid;

                    var oWinContext = new WinProductsDataContext(Convert.ToString(HttpContext.Current.Application["constr"]));
                    if (oWinContext.DatabaseExists())
                    {
                        Product oProduct = oWinContext.Products.Where(x => x.ID == oItem.ID).FirstOrDefault();
                        if (oProduct.ID> 0)
                        {
                            //means record exist
                            oWinContext.Products.DeleteOnSubmit(oProduct);
                            oWinContext.SubmitChanges(ConflictMode.FailOnFirstConflict);
                            if (oProduct != null && oProduct.ID > 0)
                            {
                                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Success, msgJson = "Record removed successfully" };
                                try
                                {
                                    //delete product details
                                    var oItemfoundCount1 = oWinContext.Product_Images.Where(x => x.PID == oItem.ID).FirstOrDefault();
                                    if (oItemfoundCount1!=null)
                                        oWinContext.Product_Images.DeleteOnSubmit(oItemfoundCount1);

                                    var oItemfoundCount2 = oWinContext.Product_Properties.Where(x => x.PID == oItem.ID).FirstOrDefault();
                                    if (oItemfoundCount2!=null)
                                        oWinContext.Product_Properties.DeleteOnSubmit(oItemfoundCount2);

                                    var oItemfoundCount3 = oWinContext.Product_Stats.Where(x => x.PID == oItem.ID).FirstOrDefault();
                                    if (oItemfoundCount3!=null)
                                        oWinContext.Product_Stats.DeleteOnSubmit(oItemfoundCount3);

                                    var oItemfoundCount4 = oWinContext.Product_User_Ratings.Where(x => x.PID == oItem.ID).FirstOrDefault();
                                    if (oItemfoundCount4!=null)
                                        oWinContext.Product_User_Ratings.DeleteOnSubmit(oItemfoundCount4);
                                }
                                catch (Exception ex)
                                {
                                    
                                }
                            }
                            else
                            {
                                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.RecordNotDeleted, msgJson = "No record deleted" };
                            }
                        }
                        else
                        {
                            oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.NoRecordsFound, msgJson = "No record found" };
                        }
                    }
                }
            }
            catch (ChangeConflictException e)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", e.ToString()) };
            }
            catch (Exception ex)
            {
                oResult = new ReturnResult() { errCode = (int)cEnums.eErrorCodes.Exception, msgJson = string.Format("Exception: {0}", ex.ToString()) };
            }
            return oResult;

        }
        #endregion

        /*Converts DataTable To List*/
        public static List<TSource> ToList<TSource>(DataTable dataTable) where TSource : new()
        {
            var dataList = new List<TSource>();

            const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
            var objFieldNames = (from PropertyInfo aProp in typeof(TSource).GetProperties(flags)
                                 select new
                                 {
                                     Name = aProp.Name,
                                     Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType
                                 }).ToList();
            var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns
                                     select new { Name = aHeader.ColumnName, Type = aHeader.DataType }).ToList();
            var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();

            foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
            {
                var aTSource = new TSource();
                foreach (var aField in commonFields)
                {
                    PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name);
                    propertyInfos.SetValue(aTSource, dataRow[aField.Name], null);
                }
                dataList.Add(aTSource);
            }
            return dataList;
        }
    }
}